package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.Customer;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer>, JpaSpecificationExecutor<Customer> {

    List<Customer> findByTelephone(String telephone);

    List<Customer> findByTelephoneAndConnHouseIdAndStatus(String telephone,int connHouseId, int status);

    int countByConnUserIdAndType(Integer id, int type);

    int countByConnUserIdAndStatusNotAndStatusNot(Integer id, int status1,int status2);

    int countByConnUserIdAndStatus(Integer id, int state);

    @Modifying
    @Query(value="update customer set status = 6 where status = :status and view_time < NOW();",nativeQuery=true)
    void updateByStatusAndViewTimeGatherThan(@Param(value = "status")int status);

    @Modifying
    @Query(value="update customer set status = 6 where status = :status and keep_time < NOW();",nativeQuery=true)
    void updateByStatusAndKeepTimeGatherThan(@Param(value = "status")int status);

    Page<Customer> findByConnHouseId(Pageable pageable, int connHouseId);

    @Query("select id from Customer where name like %?1%")
    List<String> findByNameLike(String businessManagerName);

    @Query("select count(id) from Customer where conn_user_id = ?1 and status = ?2 and last_operate_date between ?3 and ?4")
    int countByConnUserIdAndStatusAndLastOperateDateBetween(Integer uid, int status, Date startTime, Date endTime);

    /*@Query(value = " SELECT c.conn_house_id connHouseId,c.conn_user_id connUserId,c.insert_date insertTime,c.`name`,c.order_description description, " +
            " c.sex,c.telephone,u.`name` userName,hd.`name` houseName FROM customer c " +
            " LEFT JOIN `user` u ON c.conn_user_id = u.id " +
            " LEFT JOIN house_detail hd ON c.conn_house_id = hd.id " +
            "where conn_user_id = ?1 and reported_type = 1", nativeQuery = true)*/
    @Query(value = " SELECT * from customer where conn_house_id = ?1 and reported_personnel like %?2% and reported_type = 1", nativeQuery = true)
    List<Customer>  getCustomerList(Integer houseId,String level);

    @Modifying
    @Query(value="update customer set reported_type = 0,approval_name = ?2,approval_id = ?3,approval_time=?4,reported_type = ?5  where id = ?1",nativeQuery=true)
    int updateReportedType(String customerId,String userName,String userId,Date approvalTime,String reportedType);

    @Query(value = "select name from customer where  id = ?1",nativeQuery = true)
    String getName(String customerId);

}
